﻿CREATE PROCEDURE [dbo].[spMaint_InitTempDB]
AS

SET NOCOUNT ON;
/*
See this URL for details:
http://www.brentozar.com/archive/2016/01/cheat-sheet-how-to-configure-tempdb-for-microsoft-sql-server/?utm_source=Brent+Ozar+Unlimited%C2%AE+List&utm_campaign=c5b57f6dc9-RSS_EMAIL_CAMPAIGN&utm_medium=email&utm_term=0_8e3e861dd9-c5b57f6dc9-296436205
*/
--- Declare Local Variables
DECLARE	@major int
DECLARE @tempdrive TABLE ([drive_letter] char(1))
DECLARE @tempfiles TABLE ([type] char(1), [fileCount] int)
DECLARE @d TABLE (dbd_drive nvarchar(256), dbd_capacityMB varchar(256), dbd_usedMB varchar(256), dbd_freeMB varchar(256), dbd_free_pct varchar(6))
DECLARE @countdrive int, @driveletter char(1), @drivesize bigint, @maxfilesize bigint, @newfilesize bigint, 
		@sSQL varchar(MAX), @newfilecount int, @tempdir varchar(MAX)

--- Determine the type/version of SQL Server installed
SELECT		@major = CAST(PARSENAME(CONVERT(varchar,SERVERPROPERTY('ProductVersion')),4) AS int)

--- If SQL 2016 (or higher) skip this work
IF @major >= 13
BEGIN
	RETURN
END
ELSE
BEGIN
	--- Determine the drives that have temp files
	INSERT INTO @tempdrive
		SELECT		DISTINCT SUBSTRING([physical_name], 1, 1) [drive_letter]
		FROM		[sys].[master_files]
		WHERE		DB_NAME([database_id]) = 'tempdb'
	SET @countdrive = @@ROWCOUNT

	--- Error out if there are files on more than one drive
	IF @countdrive > 1
	BEGIN
		RAISERROR('All tempdb files should be on the same drive and are not.', 0, 1) WITH NOWAIT
		RETURN
	END
	ELSE
	BEGIN
		--- Determine the drive letter of the temp files
		SELECT	TOP 1 @driveletter = [drive_letter]
		FROM	@tempdrive

		IF EXISTS (SELECT TOP 1 [name] FROM [sys].[master_files] WHERE DB_NAME([database_id]) <> 'tempdb' AND [physical_name] LIKE (@driveletter + '%'))
		  BEGIN
			RAISERROR('tempdb files should be on their own drive and are not.', 0, 1) WITH NOWAIT
			RETURN
		  END

		--- Determine the current count of temp files
		INSERT INTO @tempfiles
			SELECT		[type], COUNT(*)
			FROM		[sys].[master_files]
			WHERE		DB_NAME([database_id]) = 'tempdb'
			GROUP BY	[type]

		--- Get the tempdb directory name
		SELECT		TOP 1 @tempdir = [dbo].[fnIO_DirectoryName]([physical_name])
		FROM		[sys].[master_files]
		WHERE		DB_NAME([database_id]) = 'tempdb'
					AND [type] = 0

		--- Determine the size of the drive
		INSERT INTO @d EXEC [dbo].[spIO_GetVolumeSpace] ''

		--- Determine the amount of space to use to stay under the file system alert levels
		SELECT	TOP 1 @drivesize = [dbd_capacityMB] + (1024 - ([dbd_capacityMB] - ([dbd_capacityMB]/1024*1024))) 
		FROM	(
				SELECT	CAST(CAST([dbd_capacityMB] as decimal(18,2)) as int) [dbd_capacityMB]
				FROM	@d
				WHERE	[dbd_drive] = @driveletter + ':'
				) t1 
		SELECT	@maxfilesize = (@drivesize * .80) + (1024 - ((@drivesize * .80) - ((@drivesize * .80)/1024*1024)))

		--- Determine the desired size per file
		SELECT	@newfilesize = (@maxfilesize / 9)/1024*1024

		--- Pick the 512 KB increment that is closest to the desired size
		SELECT	@newfilesize = (CASE	WHEN ABS((@maxfilesize/9) - @newfilesize) < ABS((@maxfilesize/9) - (@newfilesize+512))
												AND ABS((@maxfilesize/9) - @newfilesize) < ABS((@maxfilesize/9) - (@newfilesize+1024))
												THEN @newfilesize
										WHEN ABS((@maxfilesize/9) - (@newfilesize+512)) < ABS((@maxfilesize/9) - (@newfilesize+1024))
												THEN @newfilesize+512
										ELSE @newfilesize+1024
										END)

		--- If the current tempdb file sizes are greater than the @newfilesize, shrink the files first
		IF EXISTS (SELECT TOP 1 [name] FROM [sys].[master_files] WHERE DB_NAME([database_id]) = 'tempdb' AND ([size] * 8.0 / 1024) > @newfilesize)
		  BEGIN
			PRINT 'Shrink Files'
			DECLARE crsShrinkTempDB CURSOR FOR
				SELECT		REPLACE('USE [tempdb]; DBCC SHRINKFILE (N''[VAR_FILENAME]'' , 4)', '[VAR_FILENAME]', [name])
				FROM		[sys].[master_files]
				WHERE		DB_NAME([database_id]) = 'tempdb'
							AND CONVERT(int, [size] * 8.0 / 1024) > @newfilesize
			OPEN crsShrinkTempDB
			FETCH NEXT FROM crsShrinkTempDB INTO @sSQL
			WHILE @@FETCH_STATUS <> -1
			  BEGIN
				PRINT	@sSQL
				EXEC	(@sSQL)
				FETCH NEXT FROM crsShrinkTempDB INTO @sSQL
			  END
			CLOSE crsShrinkTempDB
			DEALLOCATE crsShrinkTempDB
		  END

		--- Grow the existing files
		IF EXISTS (SELECT TOP 1 [name] FROM [sys].[master_files] WHERE DB_NAME([database_id]) = 'tempdb' AND ([size] * 8.0 / 1024) < @newfilesize)
		  BEGIN
			PRINT 'Grow Files'
			DECLARE crsGrowTempDB CURSOR FOR
				SELECT		REPLACE(
								REPLACE('ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N''[VAR_FILENAME]'', SIZE = [VAR_NEWSIZE]MB , FILEGROWTH = 10240KB )', '[VAR_FILENAME]', [name])
							, '[VAR_NEWSIZE]', CAST(@newfilesize as varchar(100)))
				FROM		[sys].[master_files]
				WHERE		DB_NAME([database_id]) = 'tempdb'
							AND CONVERT(int, [size] * 8.0 / 1024) < @newfilesize
			OPEN crsGrowTempDB
			FETCH NEXT FROM crsGrowTempDB INTO @sSQL
			WHILE @@FETCH_STATUS <> -1
			  BEGIN
				PRINT	@sSQL
				EXEC	(@sSQL)
				FETCH NEXT FROM crsGrowTempDB INTO @sSQL
			  END
			CLOSE crsGrowTempDB
			DEALLOCATE crsGrowTempDB
		  END

		--- Add the new files
		SELECT	@newfilecount = (1 + [fileCount])
		FROM	@tempfiles
		WHERE	[type] = 0

		IF @newfilecount < 9
		BEGIN
			PRINT 'Adding additional tempdb files'

			WHILE @newfilecount < 9
			BEGIN
				SELECT @sSQL = REPLACE(REPLACE(REPLACE('
				ALTER DATABASE [tempdb] ADD FILE (
					NAME = N''tempdev_file[VAR_FILENUM]'',
					FILENAME = N''[VAR_TEMPDIR]\tempdev_file[VAR_FILENUM].ndf'',
					SIZE = [VAR_NEWSIZE]MB,
					FILEGROWTH = 10240KB
					)', '[VAR_FILENUM]', CAST((@newfilecount) as varchar(100)))
					, '[VAR_NEWSIZE]', CAST(@newfilesize as varchar(100)))
					, '[VAR_TEMPDIR]', @tempdir)

				PRINT	@sSQL
				EXEC	(@sSQL)

				SET @newfilecount = @newfilecount + 1
			END
		END
	  END
END
GO